package com.springboot.webdemo.core.excel;


import com.springboot.webdemo.core.result.Message;
import com.springboot.webdemo.core.utils.CommonUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.*;

/**
 * @Author: 余凯
 * @Description: excel操作工具类
 * @Date: 2017/12/20
 * @Modified by：
 */
public class ExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";


    /**
     * 读取excel表头信息
     *
     * @param hColValueMap 表头信息列索引与值的对应关系map
     * @param head         调用者传递过来的表头对应关系map
     * @param sheet        当前要读的sheet
     * @param fileName     当前要读的excel文件
     * @return
     * @author 余凯
     */
    private static Message readHeader(Map<Integer, String> hColValueMap, Map<String, String> head,
                                      Row headRow, Sheet sheet, String fileName) {

        if (headRow == null) {
            log.info("文件:{},表头为空，请查看文档，并重新修改!", fileName);
            //表头为Null
            return new Message(false, "表头为空，请查看文档，并重新修改!");
        }
        Iterator<Cell> headCellList = headRow.cellIterator();
        while (headCellList.hasNext()) {
            Cell cell = headCellList.next();
            if (cell == null) {
                //空列
                log.info("文件:{},表头存在空列", fileName);
                return new Message(false, "表头存在空列!");
            }
            String cellInfo = cell.getStringCellValue();
            if (CommonUtil.isEmpty(cellInfo)) {
                //空列
                log.info("文件:{},表头存在空列", fileName);
                return new Message(false, "表头存在空列!");
            }
            if (head != null && head.size() > 0 && !head.containsKey(cellInfo)) {
                //表头内容与模板不一致
                //空列
                log.info("文件:{},表头内容:{},与模板表头内容不一致", fileName, cellInfo);
                return new Message(false, "表头内容:" + cellInfo + ",与模板表头内容不一致!");
            }
            //根据headMap,返回正常的数据格式
            if (head != null) {
                hColValueMap.put(cell.getColumnIndex(), head.get(cellInfo));
            } else {
                hColValueMap.put(cell.getColumnIndex(), cellInfo);
            }

        }
        if (hColValueMap.size() > 0) {
            return new Message(true);
        }
        return new Message(true);
    }

    /**
     * 读取excel
     *
     * @param fullFileName 要读取excel文件名(路径)
     * @return 读取excel结果（包括错误信息及excel内容列表）
     * @author 余凯
     */
    public static Message read(String fullFileName) {
        return read(fullFileName, null, null, null);
    }

    /**
     * 读取excel
     *
     * @param fullFileName 要读取excel文件名(路径)
     * @param head         excel文件模块表头信息对应关系
     * @return 读取excel结果（包括错误信息及excel内容列表）
     * @author 余凯
     */
    public static Message read(String fullFileName, Map<String, String> head) {
        return read(fullFileName, head, null, null);
    }

    /**
     * 读取excel
     *
     * @param fullFileName 要读取excel文件名(路径)
     * @param head         excel文件模块表头信息对应关系
     * @param headLine     表头在表格中的第几行，默认第一行
     * @param grids        额外要读取的单元格  grids {0,0}{}
     * @return 读取excel结果（包括错误信息及excel内容列表）
     * @author 余凯
     */
    public static Message read(String fullFileName, Map<String, String> head, Integer headLine, Integer[][] grids) {

        try {
            boolean isE2007 = false;    //判断是否是excel2007格式
            if (fullFileName.endsWith("xlsx")) {
                isE2007 = true;
            }
            InputStream input = new FileInputStream(fullFileName);  //建立输入流
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
            if (headLine == null || headLine == 0) {
                headLine = 1;
            }
            //读取excel头信息
            Row headRow = sheet.getRow(headLine - 1);
            if (headRow == null) {
                log.info("文件:{},表头为空，请查看文档，并重新修改!", fullFileName);
                return new Message(false, "表头为空，请查看文档，并重新修改!");
            }
            //保存表头的列索引与列值的对应关系
            Map<Integer, String> hColValueMap = new HashMap<Integer, String>();
            //读取/校验表头
            Message readMes = readHeader(hColValueMap, head, headRow, sheet, fullFileName);
            if (!readMes.getStatus()) {
                return readMes;
            }
            //读取表内容
            List<Map<String, Object>> excelContentList = readBody(fullFileName, sheet, headLine, hColValueMap);
            //读取额外指定的单元格
            Map<Integer, Object> extraDataMap = null;
            if (grids != null && grids.length > 0) {
                extraDataMap = new HashMap<Integer, Object>(grids.length);
                for (int i = 0; i < grids.length; i++) {
                    Integer x = grids[i][0] - 1;
                    Integer y = grids[i][1] - 1;
                    Cell cell = sheet.getRow(x).getCell(y);
                    setOneGridData(fullFileName, extraDataMap, i + 1, cell);
                }
            }
            if (input != null) {
                input.close();
            }
            if (wb != null) {
                wb.close();
            }

            if (excelContentList.size() <= 0 && extraDataMap == null) {
                //文档内容为空
                log.info("文件:{},未读取到数据!", fullFileName);
                return new Message(false, "未读取到文档数据,请检查文档!");
            }

            //读取文件成功
            Map result = new HashMap();
            result.put("dataList", excelContentList);
            result.put("extraDataMap", extraDataMap);
            return new Message(true, "读取文件成功,共读取到数据:" + excelContentList.size() + "条!", result);
        } catch (IOException ex) {
            log.error("读取文件:{}失败，错误原因：{}", fullFileName, ex);
        }
        return new Message(true);
    }

    /**
     * 读取若干个格子的数据
     *
     * @param fullFileName 文件全名，包括绝对路径与后缀
     * @param grids        {0,0}{}
     * @return
     * @author 余凯
     */
    public static Map<Integer, Object> readGrid(String fullFileName, Integer[][] grids) {
        Map<Integer, Object> retMap = new HashMap<Integer, Object>(grids.length);
        try {
            boolean isE2007 = false;    //判断是否是excel2007格式
            if (fullFileName.endsWith("xlsx")) {
                isE2007 = true;
            }
            InputStream input = new FileInputStream(fullFileName);  //建立输入流
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
            for (int i = 0; i < grids.length; i++) {
                Integer x = grids[i][0] - 1;
                Integer y = grids[i][1] - 1;
                Cell cell = sheet.getRow(x).getCell(y);
                setOneGridData(fullFileName, retMap, i + 1, cell);
            }

        } catch (IOException ex) {
            log.error("读取文件:{}失败，错误原因：{}", fullFileName, ex);
            return retMap;
        }
        return retMap;
    }

    private static void setOneGridData(String fullFileName, Map gridDataMap, Object key, Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {   //根据cell中的类型来输出数据
                case HSSFCell.CELL_TYPE_NUMERIC:
                    gridDataMap.put(key, cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridDataMap.put(key, cell.getStringCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridDataMap.put(key, cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    gridDataMap.put(key, cell.getCellFormula());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    gridDataMap.put(key, null);
                    break;
                default:
                    log.info("文件:{},单元格{}的类型不支持", fullFileName, cell.getAddress());
                    gridDataMap.put(key, null);
                    break;
            }
        }
    }

    /**
     * 读取excel表内容(除表头外的信息)
     *
     * @param fullFileName 需要读取的excel文件
     * @param sheet        需求读取的excel文件内的sheet
     * @param hColValueMap 表头信息列索引与值的对应关系map
     * @return excel内容列表
     * @author 余凯
     */
    private static List<Map<String, Object>> readBody(String fullFileName, Sheet sheet, Integer headLine, Map<Integer, String> hColValueMap) {
        Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
        List<Map<String, Object>> excelContentList = new ArrayList<Map<String, Object>>();
        while (rows.hasNext()) {
            Row row = rows.next();  //获得行数据
            if (row.getRowNum() <= headLine - 1) {
                //略过表头
                continue;
            }
            //保存一行内容的map
            if (hColValueMap.size() > 0) {
                Map<String, Object> rowContentMap = new HashMap<String, Object>();
                Iterator<Cell> cells = row.cellIterator();    //获得行迭代器
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    setOneGridData(fullFileName, rowContentMap, hColValueMap.get(cell.getColumnIndex()), cell);
                }
                if (rowContentMap.size() > 0) {
                    excelContentList.add(rowContentMap);
                }
            }
        }
        return excelContentList;
    }

    /**
     * 写excel文件(多个sheet)
     *
     * @param fileName 目标文件
     * @param head     写入文件的头信息(第一行)
     *                 key -- 对应excel文件的sheet名称，这个名称必须与body中key的名称一一对应
     *                 value -- 对应表头（第一行）信息，
     *                 key -- 列索引
     *                 value -- 列值
     * @param body     写入文件内容(第二行开始)
     *                 key -- 对应excel文件的sheet名称，这个名称必须与head中key的名称一一对应
     *                 value -- 需要写入文件的sheet内容
     *                 key -- 列索引
     *                 value -- 列值
     * @return
     */
    public static boolean writeMultiSheet(String fileName, Map<String, Map<Integer, String>> head, Map<String, List<Map<Integer, Object>>> body) {
        boolean isE2007 = false;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            isE2007 = true;
        }
        try {
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook();
            } else {
                wb = new HSSFWorkbook();
            }
            if (head != null && head.size() > 0) {
                for (String sheetName : head.keySet()) {
                    writeSingleSheet(wb, sheetName, head.get(sheetName), body.get(sheetName));
                }
            }
            OutputStream output = new FileOutputStream(fileName);  //建立输出流
            wb.write(output);
            if (output != null) {
                output.close();
            }
            if (wb != null) {
                wb.close();
            }
            return true;
        } catch (Exception e) {
            log.error("写文件:{}出错,出错原因:{}", e);
        }
        return false;
    }

    /**
     * 写单个sheet的excel文件
     *
     * @param fileName
     * @param sheetName
     * @param head
     * @param body
     * @return
     */
    public static boolean writeSingleSheet(String fileName, String sheetName, Map<Integer, String> head, List<Map<Integer, Object>> body) {
        boolean isE2007 = false;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            isE2007 = true;
        }
        try {
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook();
            } else {
                wb = new HSSFWorkbook();
            }
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            Sheet sheet = null;
            if (!CommonUtil.isEmpty(sheetName)) {
                sheet = wb.createSheet(sheetName);
            } else {
                sheet = wb.createSheet();
            }
            //设置sheet样式
            setSheet(wb, sheet);
            //创建表头
            Row headRow = sheet.createRow(0);
            for (Integer index : head.keySet()) {
                Cell headCell = headRow.createCell(index);
                headCell.setCellValue(head.get(index));
            }
            short df = wb.createDataFormat().getFormat(DATE_PATTERN);
            //写内容
            for (int i = 0; i < body.size(); i++) {
                Row bodyRow = sheet.createRow(i + 1);
                for (Integer index : body.get(i).keySet()) {
                    Cell bodyCell = bodyRow.createCell(index);
                    Object cellContent = body.get(i).get(index);
                    writeCell(cellContent, bodyCell, df);
                }
            }
            OutputStream output = new FileOutputStream(fileName);  //建立输入流
            wb.write(output);
            if (output != null) {
                output.close();
            }
            return true;
        } catch (Exception e) {
            log.error("写文件:{}出错,错误:{}", fileName, e);
        }
        return false;
    }

    /**
     * 写入单个sheet
     *
     * @param wb
     * @param sheetName
     * @param head
     * @param body
     * @author 余凯
     */
    private static void writeSingleSheet(Workbook wb, String sheetName, Map<Integer, String> head, List<Map<Integer, Object>> body) {
        // 创建Excel的工作sheet,对应到一个excel文档的tab
        Sheet sheet = wb.createSheet(sheetName);
        //设置sheet样式
        setSheet(wb, sheet);
        //创建表头
        Row headRow = sheet.createRow(0);
        for (Integer index : head.keySet()) {
            Cell headCell = headRow.createCell(index);
            headCell.setCellValue(head.get(index));
        }
        short df = wb.createDataFormat().getFormat(DATE_PATTERN);
        //写内容
        for (int i = 0; i < body.size(); i++) {
            Row bodyRow = sheet.createRow(i + 1);
            for (Integer index : body.get(i).keySet()) {
                Cell bodyCell = bodyRow.createCell(index);
                Object cellContent = body.get(i).get(index);
                writeCell(cellContent, bodyCell, df);
            }
        }
    }

    /**
     * 写各种类型的数据到cell(单元格)
     *
     * @param content 内容
     * @param cell    单元格
     * @author zhangfp
     */
    private static void writeCell(Object content, Cell cell, short df) {
        if (content != null) {
            if (content instanceof String) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue((String) content);
            } else if (content instanceof Number) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) content).doubleValue());
            } else if (content instanceof Date) {
                cell.getCellStyle().setDataFormat(df);
                cell.setCellValue((Date) content);
            } else if (content instanceof Calendar) {
                cell.setCellValue((Calendar) content);
            } else if (content instanceof Boolean) {
                cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                cell.setCellValue((Boolean) content);
            }
        }
    }

    /**
     * 设置sheet属性
     *
     * @param wb
     * @param sheet
     * @author zhangfp
     */
    private static void setSheet(Workbook wb, Sheet sheet) {
        // 设置excel每列宽度
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3500);

        // 创建字体样式
        Font font = wb.createFont();
        font.setFontName("Verdana");
        font.setBoldweight((short) 100);
        font.setFontHeight((short) 300);
        font.setColor(HSSFColor.BLUE.index);

        // 创建单元格样式
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // 设置边框
        style.setBottomBorderColor(HSSFColor.RED.index);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);

        style.setFont(font);// 设置字体
    }

    /**
     * 读取excel表内容(除表头外的信息)
     *
     * @param fileName     需要读取的excel文件
     * @param sheet        需求读取的excel文件内的sheet
     * @param hColValueMap 表头信息列索引与值的对应关系map
     * @return excel内容列表
     * @author 余凯
     */
    private static List<Map<String, Object>> readBody(String fileName, Sheet sheet, Map<Integer, String> hColValueMap) {
        Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
        List<Map<String, Object>> excelContentList = new ArrayList<Map<String, Object>>();
        while (rows.hasNext()) {
            Row row = rows.next();  //获得行数据
            if (row.getRowNum() == 0) {
                //略过表头
                continue;
            }
            //保存一行内容的map
            if (hColValueMap.size() > 0) {
                Map<String, Object> rowContentMap = new HashMap<String, Object>();
                Iterator<Cell> cells = row.cellIterator();    //获得行迭代器
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    if (cell != null) {
                        switch (cell.getCellType()) {   //根据cell中的类型来输出数据
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                rowContentMap.put(hColValueMap.get(cell.getColumnIndex()), cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                rowContentMap.put(hColValueMap.get(cell.getColumnIndex()), cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                rowContentMap.put(hColValueMap.get(cell.getColumnIndex()), cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                rowContentMap.put(hColValueMap.get(cell.getColumnIndex()), cell.getCellFormula());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                rowContentMap.put(hColValueMap.get(cell.getColumnIndex()), null);
                                break;
                            default:
                                log.info("文件:{},不支持的单元格类型", fileName);
                                break;
                        }
                    }
                }
                if (rowContentMap.size() > 0) {
                    excelContentList.add(rowContentMap);
                }
            }
        }
        return excelContentList;
    }
}
